﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace Ketnoidulieu
{
    class clssQLDiem
    {
        OleDbConnection c = new OleDbConnection();
        public clssQLDiem()
        {
            c.ConnectionString = "provider=microsoft.jet.oledb.4.0;data source=QLDiem.mdb";
            if (c.State == ConnectionState.Closed)
                c.Open();
        }

        public DataSet laydulieu()
        {
            DataSet d = new DataSet();
            OleDbDataAdapter da = new OleDbDataAdapter("select * from Lop", c);
            da.Fill(d, "Lop");
            return d;
        }

        public DataSet laydulieu1()
        {
            DataSet d = new DataSet();
            OleDbDataAdapter da = new OleDbDataAdapter("select * from Sinhvien", c);
            da.Fill(d, "sv");
            return d;
        }

        /*public DataSet laydulieu(string tenbang)
        {
            DataSet d = new DataSet();
            OleDbDataAdapter da = new OleDbDataAdapter("select * from"+tenbang, c);
            da.Fill(d, tenbang);
            return d;
        }*/

        public DataSet laydulieu(string sql)
        {
            DataSet d = new DataSet();
            OleDbDataAdapter da = new OleDbDataAdapter(sql, c);
            da.Fill(d,"xxx");
            return d;
        }

        public DataSet laydulieunhieubang()
        {
            DataSet d = new DataSet();
            OleDbDataAdapter da = new OleDbDataAdapter("select * from SinhVien", c);
            da.Fill(d, "Sinh Viên");
            da = new OleDbDataAdapter("select * from MonHoc", c);
            da.Fill(d, "Môn Học");
            da = new OleDbDataAdapter("select * from Lop", c);
            da.Fill(d, "Lớp");
            da = new OleDbDataAdapter("select * from Khoa", c);
            da.Fill(d, "Khoa");
            da = new OleDbDataAdapter("select * from Diem", c);
            da.Fill(d, "Điểm");
            c.Close();
            return d;

        }

        public DataSet laydulieulopsv()
        {
            DataSet d = new DataSet();
            OleDbDataAdapter dalop = new OleDbDataAdapter("select * from lop", c);
            OleDbDataAdapter dasv = new OleDbDataAdapter("select * from sinhvien", c);
            dalop.Fill(d, "lop");
            dasv.Fill(d, "sv");
            DataColumn cotcha = d.Tables["lop"].Columns["malop"];
            DataColumn cotcon = d.Tables["sv"].Columns["malop"];
            d.Relations.Add("lopsv", cotcha, cotcon);
            return d;
        }

        public DataSet laydulieudiemsv()
        {
            DataSet d = new DataSet();
            OleDbDataAdapter dalop = new OleDbDataAdapter("select masv,tenmh,diem,lanthi,sotc from monhoc mh,diem d where mh.mamh=d.mamh", c);
            OleDbDataAdapter dasv = new OleDbDataAdapter("select * from sinhvien", c);
            dalop.Fill(d, "lop");
            dasv.Fill(d, "sv");
            DataColumn cotcha = d.Tables["lop"].Columns["malop"];
            DataColumn cotcon = d.Tables["sv"].Columns["malop"];
            d.Relations.Add("lopsv", cotcha, cotcon);
            return d;
        }

        public DataSet laydanhsach_theothamso(string tencautruyvan, string giatrits, string tenbien)
        {
            DataSet ds = new DataSet();
            OleDbCommand cmd = new OleDbCommand();
            OleDbDataAdapter da = new OleDbDataAdapter();
            cmd.Connection = c;
            cmd.CommandText = tencautruyvan;
            cmd.CommandType = CommandType.StoredProcedure;
            OleDbParameter p = new OleDbParameter(tenbien, OleDbType.VarChar);
            p.Value = giatrits;
            cmd.Parameters.Add(p);
            da.SelectCommand = cmd;
            da.Fill(ds);
            return ds;
        }
    }
}
